﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace ZeBobo5.LibraryHelper.Core.Sql
{
    public sealed class SqlHelper
    {
        #region Gestion de la connection

        private static Dictionary<string, IDbConnection> _connections;

        public static void AddConnection(string connectionName, IDbConnection connection)
        {
            if (_connections == null)
            {
                _connections = new Dictionary<string, IDbConnection>();
            }
            _connections[connectionName] = connection;
        }

        public static void CloseConnection(string connectionName)
        {
            if (_connections != null && _connections[connectionName] != null)
            {
                _connections[connectionName].Close();
            }
        }

        public static void CloseAndDisposeAllConnections()
        {
            foreach (IDbConnection connection in _connections.Values)
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
                connection.Dispose();
            }
        }

        public static IDbConnection GetConnection(string connectionName)
        {
            if (_connections != null && _connections[connectionName] != null)
            {
                return _connections[connectionName];
            }
            throw new Exception("Connection not found.");
        }

        public static bool CouldConnectToDataBase(string connectionName)
        {
            return CouldConnectToDataBase(connectionName, false);
        }

        public static bool CouldConnectToDataBase(string connectionName, bool closeConnection)
        {
            try
            {
                if (GetConnection(connectionName).State != ConnectionState.Open)
                {
                    GetConnection(connectionName).Open();
                    if (closeConnection)
                    {
                        GetConnection(connectionName).Close();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion

        #region Scalar

        public static object ExecQueryScalar(string connectionName, string sqlQuery)
        {
            using (IDbCommand sqlComm = GetConnection(connectionName).CreateCommand())
            {
                if (GetConnection(connectionName).State != ConnectionState.Open)
                {
                    GetConnection(connectionName).Open();
                }
                sqlComm.CommandText = sqlQuery;
                WriteLog(sqlQuery);
                return sqlComm.ExecuteScalar();
            }
        }

        public static object ExecQueryScalar(string connectionName, string sqlQuery, params string[] parameters)
        {
            return ExecProcScalar(connectionName, string.Format(sqlQuery, parameters));
        }

        public static object ExecProcScalar(string connectionName, string procName)
        {
            return ExecProcScalar(connectionName, procName, null);
        }

        public static object ExecProcScalar(string connectionName, string procName, params IDbDataParameter[] parameters)
        {
            using (IDbCommand sqlComm = GetConnection(connectionName).CreateCommand())
            {
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = procName;
                if (parameters != null)
                {
                    foreach (IDbDataParameter parameter in parameters)
                    {
                        sqlComm.Parameters.Add(parameter);
                    }
                }
                if (GetConnection(connectionName).State != ConnectionState.Open)
                {
                    GetConnection(connectionName).Open();
                }
                WriteLog(procName);
                return sqlComm.ExecuteScalar();
            }
        }

        #endregion

        #region Non Query

        public static int ExecProcNonQuery(string connectionName, string procName)
        {
            return ExecProcNonQuery(connectionName, procName, null);
        }

        public static int ExecProcNonQuery(string connectionName, string procName, params IDbDataParameter[] parameters)
        {
            using (IDbCommand sqlComm = GetConnection(connectionName).CreateCommand())
            {
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = procName;
                if (parameters != null)
                {
                    foreach (IDbDataParameter parameter in parameters)
                    {
                        sqlComm.Parameters.Add(parameter);
                    }
                }
                if (GetConnection(connectionName).State != ConnectionState.Open)
                {
                    GetConnection(connectionName).Open();
                }
                WriteLog(procName);
                return sqlComm.ExecuteNonQuery();
            }
        }

        public static int ExecQueryNonQuery(string connectionName, string sqlQuery)
        {
            using (IDbCommand sqlComm = GetConnection(connectionName).CreateCommand())
            {
                if (GetConnection(connectionName).State != ConnectionState.Open)
                {
                    GetConnection(connectionName).Open();
                }
                sqlComm.CommandText = sqlQuery;
                WriteLog(sqlQuery);
                return sqlComm.ExecuteNonQuery();
            }
        }

        public static int ExecQueryNonQuery(string connectionName, string sqlQuery, params string[] parameters)
        {
            return ExecQueryNonQuery(connectionName, string.Format(sqlQuery, parameters));
        }

        #endregion

        #region To DataTable

        public static DataTable ExecQueryToDataTable(string connectionName, string sqlQuery)
        {
            DataTable table = new DataTable();
            using (IDbCommand sqlComm = GetConnection(connectionName).CreateCommand())
            {
                if (GetConnection(connectionName).State != ConnectionState.Open)
                {
                    GetConnection(connectionName).Open();
                }
                sqlComm.CommandText = sqlQuery;
                WriteLog(sqlQuery);
                using (IDataReader reader = sqlComm.ExecuteReader())
                {
                    table.Load(reader);
                }
                return table;
            }
        }

        public static DataTable ExecQueryToDataTable(string connectionName, string sqlQuery, params object[] args)
        {
            return ExecProcToDataTable(connectionName, string.Format(sqlQuery, args));
        }

        public static DataTable ExecProcToDataTable(string connectionName, string procName)
        {
            return ExecProcToDataTable(connectionName, procName, null);
        }

        public static DataTable ExecProcToDataTable(string connectionName, string procName,
                                                    params IDbDataParameter[] parameters)
        {
            DataTable table = new DataTable();
            using (IDbCommand sqlComm = GetConnection(connectionName).CreateCommand())
            {
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = procName;
                if (parameters != null)
                {
                    foreach (IDbDataParameter parameter in parameters)
                    {
                        sqlComm.Parameters.Add(parameter);
                    }
                }
                if (GetConnection(connectionName).State != ConnectionState.Open)
                {
                    GetConnection(connectionName).Open();
                }
                WriteLog(procName);
                using (IDataReader reader = sqlComm.ExecuteReader())
                {
                    table.Load(reader);
                }
                return table;
            }
        }

        #endregion

        #region Log

        private static StreamWriter _log;

        public static StreamWriter Log
        {
            get { return _log; }
            set { _log = value; }
        }

        public static void WriteLog(string value)
        {
            if (_log != null)
            {
                _log.WriteLine(value);
            }
        }

        #endregion
    }
}